// ----------------------------------------------------------------------------//
// SQLUtils.java
// Created by user On 2012-10-17
package com.demo.framework.db;

import java.nio.ByteBuffer;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * 数据库操作实用方法
 */
public class SqliteUtils {
    static private final Logger LOG = LoggerFactory.getLogger(SqliteUtils.class);

    static private final String HEXES = "0123456789ABCDEF";

    static public long insert(SQLiteDatabase db, String tableName, ContentValues values) {
        return db.insertOrThrow(tableName, null, values);
    }

    static public int update(SQLiteDatabase db, String tableName, ContentValues values, String whereClause,
            String... whereArgs) {
        return db.update(tableName, values, whereClause, whereArgs);
    }

    static public void execSQL(SQLiteDatabase db, String sql) {
        LOG.debug("[DB OPERATION] Exec SQL \"{}\"", sql);
        db.execSQL(sql);
    }

    static public Cursor query(SQLiteDatabase db, String sql, String... args) {
        _log_query(sql);
        return db.rawQuery(sql, args);
    }

    static public <T> T queryObject(SQLiteDatabase db, String sql, QueryResultHandler<T> objq, String... args) {
        _log_query(sql);
        Cursor c = null;
        T obj;
        try {
            c = db.rawQuery(sql, args);
            c.moveToFirst();
            obj = c.isAfterLast() ? null : objq.handle(c, c.getColumnCount());
        } finally {
            if (c != null)
                c.close();
        }
        return obj;
    }

    static public <T> List<T> queryList(SQLiteDatabase db, String sql, QueryResultHandler<T> objq, String... args) {
        _log_query(sql);
        Cursor c = null;
        List<T> results;
        try {
            c = db.rawQuery(sql, args);
            int n = c.getColumnCount();
            c.moveToFirst();
            results = new ArrayList<T>();
            while (!c.isAfterLast()) {
                results.add(objq.handle(c, n));
                c.moveToNext();
            }
        } finally {
            if (c != null)
                c.close();
        }
        return results;
    }

    static public void queryWithHandler(SQLiteDatabase db, String sql, QueryResultHandler<Void> rh, String... args) {
        _log_query(sql);
        Cursor c = null;
        try {
            c = db.rawQuery(sql, args);
            int n = c.getColumnCount();
            c.moveToFirst();
            while (!c.isAfterLast()) {
                rh.handle(c, n);
                c.moveToNext();
            }
        } finally {
            LOG.debug("Close cursor after RowHandler");
            if (c != null)
                c.close();
        }
    }

//    static public void createTableIfNoExists(SQLiteDatabase db, String tableName, String[] columns) {
//        LOG.debug("[DB OPERATION] Creating table {} ...", tableName);
//        String sql = String.format("CREATE TABLE IF NOT EXISTS %s(%s)", tableName, StringUtils.join(columns, ","));
//        _log_sql(sql);
//        db.execSQL(sql);
//    }

    static public void dropTableIfExists(SQLiteDatabase db, String tableName) {
        LOG.debug("[DB OPERATION] Droping Table {} ...", tableName);
        String sql = String.format("DROP TABLE IF EXISTS %s", tableName);
        _log_sql(sql);
        db.execSQL(sql);
    }

    static final public String newID() {
        return UUID.randomUUID().toString();
    }

    static final public String sqliteHex(byte[] raw) {
        if (raw == null) {
            return null;
        }
        final StringBuilder hex = new StringBuilder(2 * raw.length);
        for (final byte b : raw) {
            hex.append(HEXES.charAt((b & 0xF0) >> 4)).append(HEXES.charAt((b & 0x0F)));
        }
        return hex.toString();
    }

    static final public String sqliteBlobLiteral(byte[] blob) {
        String s = sqliteHex(blob);
        return "X'" + s + "'";
    }

    static final public UUID UUIDFromBlob(byte[] blob) {
        if (blob == null || blob.length != 16)
            return null;
        ByteBuffer buf = ByteBuffer.wrap(blob);
        long h = buf.getLong();
        long l = buf.getLong();
        return new UUID(h, l);
    }

    static final public byte[] UUIDtoByteArray(UUID id) {
        assert id != null;
        ByteBuffer buf = ByteBuffer.allocate(16);
        buf.putLong(id.getMostSignificantBits());
        buf.putLong(id.getLeastSignificantBits());
        return buf.array();
    }

    static final private void _log_query(String sql) {
        LOG.debug("[DB OPERATION] Query \"{}\"", sql);
    }

    static final private void _log_sql(String sql) {
        LOG.debug(">> SQL = {}", sql);
    }
}

// ----------------------------------END---------------------------------------//